﻿<cfsilent>
	<cfscript>
	
		termID = event.getArg("TermID");
		depID = event.getArg("DepID");
		
		sql = "SELECT institute_name
				FROM t_institute 
				WHERE institute_id = :instituteId " ;
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="instituteId", value=depID, cfsqltype="cf_sql_varchar" );
		rs_departmentEntity = queryObj.execute( sql=sql ).getResult();
	
		sql = "SELECT
					t_class.cls_name, 
					t_class.cls_size, 
					t_subject.sbj_short, 
					t_class.grade, 
					t_plan.sbj_direction, 
					t_course.course_name, 
					t_course_nature.cna_name, 
					t_course_prop.prop_name, 
					t_course.course_credit, 
					t_institute.institute_short, 
					t_course.period_theory, 
					t_course.period_practice, 
					t_course.period_computer, 
					t_course.course_code 
				FROM 
					t_plan 
					INNER JOIN t_class ON t_class.cls_id = t_plan.cls_id 
					INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id 
					INNER JOIN t_course ON t_course.cid = t_plan.cid 
					INNER JOIN t_course_nature ON t_course_nature.cna_id = t_plan.cna_id 
					INNER JOIN t_course_prop ON t_course_prop.prop_id = t_plan.prop_id 
					INNER JOIN t_institute ON t_institute.institute_id = t_course.institute_id 
				WHERE 
				    t_plan.term_id = :term 
				    AND 
				    t_subject.institute_id = :dep 
				ORDER BY 
					t_plan.sbj_direction, t_course_prop.prop_name, t_course.course_name " ;
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="term", value=termID, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="dep", value=depID, cfsqltype="cf_sql_varchar" );
		rs_plan = queryObj.execute( sql=sql ).getResult();
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "学期教学计划", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("班级教学计划", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "班级,计划人数,专业简称,年级,修读方向,课程,课程类别,课程性质,学分,任课单位,理论学时,实践学时,上机学时,课程号");
		
		for ( row=1; row LTE rs_plan.recordCount; row++){
	
			/* 构建计划信息 */
			rowData = rs_plan["cls_name"][row]
						& "," & rs_plan["cls_size"][row]
						& "," & rs_plan["sbj_short"][row]
						& "," & rs_plan["grade"][row]
						& "," & rs_plan["sbj_direction"][row]
						& "," & rs_plan["course_name"][row]
						& "," & rs_plan["cna_name"][row]
						& "," & rs_plan["prop_name"][row]
						& "," & numberFormat( rs_plan["course_credit"][row], '_.__' )
						& "," & rs_plan["institute_short"][row]
						& "," & rs_plan["period_theory"][row]
						& "," & rs_plan["period_practice"][row]
						& "," & rs_plan["period_computer"][row]
						& "," & rs_plan["course_code"][row];
			
			/* 写入计划信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>